import sqlite3
import os
from pathlib import Path

def initialize_database():
    """初始化文言文语料数据库"""
    # 确定数据库路径
    base_dir = Path(__file__).parent.parent
    data_dir = base_dir / "data"
    data_dir.mkdir(exist_ok=True)  # 确保数据目录存在
    db_path = data_dir / "wenyan_corpus.db"
    
    # 连接数据库
    conn = sqlite3.connect(str(db_path))
    cursor = conn.cursor()
    
    print("📚 正在创建古汉语编程语料库...")
    print(f"📁 数据库位置: {db_path}")

    # 检查是否已存在表
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
    existing_tables = [table[0] for table in cursor.fetchall()]
    
    if existing_tables:
        print("⚠️ 数据库已存在以下表:")
        for table in existing_tables:
            print(f" - {table}")
        print("🔍 跳过表创建...")
    else:
        # 创建文言文文本表（增强版）
        cursor.execute('''
        CREATE TABLE classical_corpus (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            original_text TEXT NOT NULL,
            modern_translation TEXT,
            dynasty TEXT,
            author TEXT,
            source_book TEXT,
            word_count INTEGER,
            difficulty_level INTEGER,
            tags TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')

        # 字词分析表
        cursor.execute('''
        CREATE TABLE word_analysis (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            corpus_id INTEGER,
            character TEXT NOT NULL,
            pinyin TEXT,
            meaning TEXT,
            usage_examples TEXT,
            frequency INTEGER DEFAULT 0,
            FOREIGN KEY (corpus_id) REFERENCES classical_corpus (id)
        )
        ''')

        # 语法规则表
        cursor.execute('''
        CREATE TABLE grammar_rules (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            rule_name TEXT NOT NULL,
            pattern TEXT NOT NULL,
            explanation TEXT,
            examples TEXT,
            difficulty_level INTEGER
        )
        ''')

        # AI训练数据表
        cursor.execute('''
        CREATE TABLE training_data (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            input_text TEXT NOT NULL,
            expected_output TEXT,
            context_info TEXT,
            used_in_training BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')

        print("✅ 文言文语料数据库初始化完成！")
        print("📊 已创建4个核心数据表：")
        print(" - classical_corpus（文言文本库）")
        print(" - word_analysis（字词分析）")
        print(" - grammar_rules（语法规则）")
        print(" - training_data（训练数据）")

    # 插入一些示例数据（如果表是新建的）
    if not existing_tables:
        try:
            # 插入示例文言文
            cursor.execute('''
            INSERT INTO classical_corpus (title, original_text, modern_translation, dynasty, author, word_count, difficulty_level)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', ('论语·学而', '学而时习之，不亦说乎？', '学习并且时常复习，不是很愉快吗？', '春秋', '孔子', 6, 2))

            cursor.execute('''
            INSERT INTO classical_corpus (title, original_text, modern_translation, dynasty, author, word_count, difficulty_level)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', ('道德经·第一章', '道可道，非常道。名可名，非常名。', 
                  '可以用言语表达的道，就不是永恒的道。可以用名称界定的名，就不是永恒的名。', 
                  '春秋', '老子', 12, 3))

            conn.commit()
            print("📝 已插入示例文言文数据")
            
        except Exception as e:
            print(f"⚠️ 插入示例数据时出错: {e}")
            conn.rollback()

    # 显示数据库信息
    cursor.execute("SELECT COUNT(*) FROM classical_corpus")
    count = cursor.fetchone()[0]
    print(f"📖 当前文言文条目数: {count}")
    
    conn.close()

if __name__ == "__main__":
    initialize_database()